
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/12/2012 17:25:05
-- Generated from EDMX file: D:\Jobs\Jobs\DataModels\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Jobs];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Education_Resume]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Education] DROP CONSTRAINT [FK_Education_Resume];
GO
IF OBJECT_ID(N'[dbo].[FK_Experience_Resume]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Experience] DROP CONSTRAINT [FK_Experience_Resume];
GO
IF OBJECT_ID(N'[dbo].[FK_JobsResume_Jobs]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobsResume] DROP CONSTRAINT [FK_JobsResume_Jobs];
GO
IF OBJECT_ID(N'[dbo].[FK_Resume_JobSeeker]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Resume] DROP CONSTRAINT [FK_Resume_JobSeeker];
GO
IF OBJECT_ID(N'[dbo].[FK_JobsResume_Resume]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobsResume] DROP CONSTRAINT [FK_JobsResume_Resume];
GO
IF OBJECT_ID(N'[dbo].[FK_Security_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Security] DROP CONSTRAINT [FK_Security_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_JobSeeker_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Person_JobSeeker] DROP CONSTRAINT [FK_JobSeeker_inherits_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_Company_inherits_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Person_Company] DROP CONSTRAINT [FK_Company_inherits_Person];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Education]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Education];
GO
IF OBJECT_ID(N'[dbo].[Experience]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Experience];
GO
IF OBJECT_ID(N'[dbo].[Jobs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Jobs];
GO
IF OBJECT_ID(N'[dbo].[JobsResume]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobsResume];
GO
IF OBJECT_ID(N'[dbo].[Person]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Person];
GO
IF OBJECT_ID(N'[dbo].[Resume]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Resume];
GO
IF OBJECT_ID(N'[dbo].[Security]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Security];
GO
IF OBJECT_ID(N'[dbo].[Person_JobSeeker]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Person_JobSeeker];
GO
IF OBJECT_ID(N'[dbo].[Person_Company]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Person_Company];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Education'
CREATE TABLE [dbo].[Education] (
    [EducationId] int IDENTITY(1,1) NOT NULL,
    [ResumeId] int  NOT NULL,
    [FullName] nvarchar(50)  NOT NULL,
    [StartTime] datetime  NOT NULL,
    [EndTime] datetime  NULL,
    [Description] nvarchar(200)  NULL
);
GO

-- Creating table 'Experience'
CREATE TABLE [dbo].[Experience] (
    [ExperienceId] int IDENTITY(1,1) NOT NULL,
    [ResumeId] int  NOT NULL,
    [FullName] nvarchar(50)  NOT NULL,
    [StartTime] datetime  NOT NULL,
    [EndTime] datetime  NULL,
    [Description] nvarchar(500)  NOT NULL,
    [AtCompany] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Jobs'
CREATE TABLE [dbo].[Jobs] (
    [JobId] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(50)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [ContactEmail] nvarchar(100)  NOT NULL,
    [ContactPhone] varchar(11)  NOT NULL,
    [StartTime] datetime  NOT NULL,
    [EndTime] datetime  NULL,
    [CompanyId] nvarchar(max)  NOT NULL,
    [Company_PersonId] int  NOT NULL
);
GO

-- Creating table 'JobsResume'
CREATE TABLE [dbo].[JobsResume] (
    [JobId] int  NOT NULL,
    [ResumeId] int  NOT NULL,
    [AppliedTime] datetime  NOT NULL,
    [IsRead] bit  NOT NULL
);
GO

-- Creating table 'Person'
CREATE TABLE [dbo].[Person] (
    [PersonId] int IDENTITY(1,1) NOT NULL,
    [FullName] nvarchar(50)  NOT NULL,
    [RegisterTime] datetime  NOT NULL,
    [LastLoginTime] datetime  NULL,
    [Email] nvarchar(50)  NULL,
    [Mobile] varchar(11)  NULL
);
GO

-- Creating table 'Resume'
CREATE TABLE [dbo].[Resume] (
    [ResumeId] int IDENTITY(1,1) NOT NULL,
    [JobSeekerId] int  NOT NULL,
    [FullName] nvarchar(5)  NOT NULL,
    [Age] int  NOT NULL,
    [Gender] int  NOT NULL
);
GO

-- Creating table 'Security'
CREATE TABLE [dbo].[Security] (
    [Username] nvarchar(50)  NOT NULL,
    [Password] nvarchar(50)  NOT NULL,
    [PersonId] int  NOT NULL
);
GO

-- Creating table 'Person_JobSeeker'
CREATE TABLE [dbo].[Person_JobSeeker] (
    [JobSeekerId] int IDENTITY(1,1) NOT NULL,
    [Education] nvarchar(50)  NULL,
    [PersonId] int  NOT NULL
);
GO

-- Creating table 'Person_Company'
CREATE TABLE [dbo].[Person_Company] (
    [CompanyId] int IDENTITY(1,1) NOT NULL,
    [Address] nvarchar(100)  NULL,
    [PersonId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [EducationId] in table 'Education'
ALTER TABLE [dbo].[Education]
ADD CONSTRAINT [PK_Education]
    PRIMARY KEY CLUSTERED ([EducationId] ASC);
GO

-- Creating primary key on [ExperienceId] in table 'Experience'
ALTER TABLE [dbo].[Experience]
ADD CONSTRAINT [PK_Experience]
    PRIMARY KEY CLUSTERED ([ExperienceId] ASC);
GO

-- Creating primary key on [JobId] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [PK_Jobs]
    PRIMARY KEY CLUSTERED ([JobId] ASC);
GO

-- Creating primary key on [JobId], [ResumeId] in table 'JobsResume'
ALTER TABLE [dbo].[JobsResume]
ADD CONSTRAINT [PK_JobsResume]
    PRIMARY KEY CLUSTERED ([JobId], [ResumeId] ASC);
GO

-- Creating primary key on [PersonId] in table 'Person'
ALTER TABLE [dbo].[Person]
ADD CONSTRAINT [PK_Person]
    PRIMARY KEY CLUSTERED ([PersonId] ASC);
GO

-- Creating primary key on [ResumeId] in table 'Resume'
ALTER TABLE [dbo].[Resume]
ADD CONSTRAINT [PK_Resume]
    PRIMARY KEY CLUSTERED ([ResumeId] ASC);
GO

-- Creating primary key on [Username] in table 'Security'
ALTER TABLE [dbo].[Security]
ADD CONSTRAINT [PK_Security]
    PRIMARY KEY CLUSTERED ([Username] ASC);
GO

-- Creating primary key on [PersonId] in table 'Person_JobSeeker'
ALTER TABLE [dbo].[Person_JobSeeker]
ADD CONSTRAINT [PK_Person_JobSeeker]
    PRIMARY KEY CLUSTERED ([PersonId] ASC);
GO

-- Creating primary key on [PersonId] in table 'Person_Company'
ALTER TABLE [dbo].[Person_Company]
ADD CONSTRAINT [PK_Person_Company]
    PRIMARY KEY CLUSTERED ([PersonId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ResumeId] in table 'Education'
ALTER TABLE [dbo].[Education]
ADD CONSTRAINT [FK_Education_Resume]
    FOREIGN KEY ([ResumeId])
    REFERENCES [dbo].[Resume]
        ([ResumeId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Education_Resume'
CREATE INDEX [IX_FK_Education_Resume]
ON [dbo].[Education]
    ([ResumeId]);
GO

-- Creating foreign key on [ResumeId] in table 'Experience'
ALTER TABLE [dbo].[Experience]
ADD CONSTRAINT [FK_Experience_Resume]
    FOREIGN KEY ([ResumeId])
    REFERENCES [dbo].[Resume]
        ([ResumeId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Experience_Resume'
CREATE INDEX [IX_FK_Experience_Resume]
ON [dbo].[Experience]
    ([ResumeId]);
GO

-- Creating foreign key on [JobId] in table 'JobsResume'
ALTER TABLE [dbo].[JobsResume]
ADD CONSTRAINT [FK_JobsResume_Jobs]
    FOREIGN KEY ([JobId])
    REFERENCES [dbo].[Jobs]
        ([JobId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [JobSeekerId] in table 'Resume'
ALTER TABLE [dbo].[Resume]
ADD CONSTRAINT [FK_Resume_JobSeeker]
    FOREIGN KEY ([JobSeekerId])
    REFERENCES [dbo].[Person_JobSeeker]
        ([PersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Resume_JobSeeker'
CREATE INDEX [IX_FK_Resume_JobSeeker]
ON [dbo].[Resume]
    ([JobSeekerId]);
GO

-- Creating foreign key on [ResumeId] in table 'JobsResume'
ALTER TABLE [dbo].[JobsResume]
ADD CONSTRAINT [FK_JobsResume_Resume]
    FOREIGN KEY ([ResumeId])
    REFERENCES [dbo].[Resume]
        ([ResumeId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_JobsResume_Resume'
CREATE INDEX [IX_FK_JobsResume_Resume]
ON [dbo].[JobsResume]
    ([ResumeId]);
GO

-- Creating foreign key on [PersonId] in table 'Security'
ALTER TABLE [dbo].[Security]
ADD CONSTRAINT [FK_Security_Person]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[Person]
        ([PersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Security_Person'
CREATE INDEX [IX_FK_Security_Person]
ON [dbo].[Security]
    ([PersonId]);
GO

-- Creating foreign key on [Company_PersonId] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [FK_CompanyJobs]
    FOREIGN KEY ([Company_PersonId])
    REFERENCES [dbo].[Person_Company]
        ([PersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompanyJobs'
CREATE INDEX [IX_FK_CompanyJobs]
ON [dbo].[Jobs]
    ([Company_PersonId]);
GO

-- Creating foreign key on [PersonId] in table 'Person_JobSeeker'
ALTER TABLE [dbo].[Person_JobSeeker]
ADD CONSTRAINT [FK_JobSeeker_inherits_Person]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[Person]
        ([PersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [PersonId] in table 'Person_Company'
ALTER TABLE [dbo].[Person_Company]
ADD CONSTRAINT [FK_Company_inherits_Person]
    FOREIGN KEY ([PersonId])
    REFERENCES [dbo].[Person]
        ([PersonId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------